<?php
	class MysqliGenerator
	{
		/**
		 * Generates stored procedures
		 *
		 * @param array $data necessary data
		 */
		public function GenerateStoredProcedures($data)
		{
			$procedures = array(
			$proc_name => $this->GenerateStoredProcedureInsert($data, $proc_name),
			$proc_name => $this->GenerateStoredProcedureUpdate($data, $proc_name), 
			$proc_name => $this->GenerateStoredProcedureDelete($data, $proc_name),
			$proc_name => $this->GenerateStoredProcedureGet($data, $proc_name),
			$proc_name => $this->GenerateStoredProcedureList($data, $proc_name), 
			$proc_name => $this->GenerateStoredProcedureListPaged($data, $proc_name));
			return $procedures;
		}			
		
		function GenerateStoredProcedureListPaged($data, &$procedure_name)		
		{
			global $current_controller;
			
			//getting the table_name to sift out fields from other tables
			$primary_table_name = $this->FindTableName($data);
			
			if ($current_controller->tree_structure == true)
			{
				$parent_id_variable = "@parent_id varchar(100) ='0',";	
				$parent_id_declare = "DECLARE @PARENT_STRING varchar(8000)";
				$parent_id_condition = "if (@parent_id = '-1') SET @PARENT_STRING = ''
										else SET @PARENT_STRING = 'AND $primary_table_name.$current_controller->parentId IN ('+@parent_id+')';";
				$parent_string = "' + @PARENT_STRING + '";
			}
			else 
			{
				 $parent_id_variable = '';
				 $parent_id_declare = '';
				 $parent_id_condition = '';
				 $parent_string = '';
			}
			
			$var_hull = new CodeGeneratorController();			
			
			//finding key field
			$key_field = $this->FindKeyField($data);
			
			//getting the root of the procedure name from a table name
			$procedure_name = $this->FindProcedureName($var_hull->storedProcedurePrefix, $var_hull->tablePrefix, $primary_table_name, '_list_paged');		
			
			//seeking for sort_by field
			$sort_by_field = $this->FindSortByField($data, $key_field[name], $var_hull);
			
			//find out if there is field to mark deleted rows
			$has_deleted_field = $this->HasDeletedField($data, $var_hull);
			
			//find out all tables take part in procedure
			$all_tables = $this->AllTables($data);
			
			//constructing table relations
			$table_relations = $this->TablesRelations($data);
			if (!isset($table_relations)) $table_relations = ' ';
			
			//if we has deleting field, we should add it list of received variabels and WHERE-clause
			$deleting_field_variables_string = '';
			$deleting_field_where_string = '';
			if ($has_deleted_field)
			{
//				$deleting_field_variables_string = "@".$var_hull->deletingFieldName." varchar = 0,";
				$deleting_field_where_string = "$primary_table_name.$var_hull->deletingFieldName =0', '";
			}
			
			//creating description of variables, lists of fields, list of variables for insert
			$fields_list = $this->TablesFields($data, "", true);
			
			//string with info about our generator
			$generated_by = $this->GeneratedBy();
			
			//generating variabels description and conditions for filtering
			$variables_description = '';
			$declared_vars = '';
			$set_vars = '';
			$conditions = '';
			$using_vars = '';
			$TEMP = "_TEMP";
			foreach ($data as $value)
			{
				if ($value[column_name] != $var_hull->deletingFieldName)
				{						
					$variables_description .= "@{$value[column_name]} varchar(max)";					
					$variables_description .= " = ''";
					$variables_description .= ",\n\t";	
					
					//if we have parent_Id than we separetely and manualy make conditions
					if ($current_controller->tree_structure)
						continue;
					
					$declared_vars .= "DECLARE @".strtoupper($value[column_name])."$TEMP"." varchar(max)";
					$declared_vars .= "\n\t";	
					
					$set_vars .= "SET @".strtoupper($value[column_name])."$TEMP = ''";
					$set_vars .= "\n\t";	
					
					$conditions .= "if (@{$value[column_name]} != '') SET @".strtoupper($value[column_name])."$TEMP='";
					if ($value[original_data_type] == 'bigint')
					{						
						$conditions .= "\n\t AND $value[table_name].$value[column_name] = ' +@$value[column_name];";
					}	
					else 
					{
						$conditions .= "\n\t AND $value[table_name].$value[column_name] LIKE ''%' +@$value[column_name]+ '%''';";
					}	
					$conditions .= "\n\t";
					
					$using_vars .= "' +@".strtoupper($value[column_name])."$TEMP+ '\n\t\t";
				}
			}
			
			//check if we should use WHERE-clause
			$WHERE = 'WHERE';
			if (empty($deleting_field_where_string) && empty($table_relations) && empty($using_vars))
				$WHERE = '';
			
			
			
//making the whole string of procedure
$procedure_string =  <<<EOS_list_paged

-- ================================================
-- $generated_by
-- ================================================
CREATE PROCEDURE `$procedure_name`
(
	IN $var_hull->pageNumber varchar(100),
	IN $var_hull->pageOffset varchar(100),
	IN $var_hull->sortBy varchar(100),
	IN $var_hull->sortMethod varchar(10),	
	OUT $var_hull->numRows BIGINT
	
)

	NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
    
BEGIN
	if ($var_hull->sortMethod IS NULL OR $var_hull->sortMethod='') THEN SET $var_hull->sortMethod = 'ASC';
     END IF;
	if ($var_hull->sortBy IS NULL OR $var_hull->sortBy='') THEN SET $var_hull->sortBy = '$primary_table_name.$sort_by_field';
     END IF;
    if ($var_hull->pageOffset IS NULL OR $var_hull->pageOffset='') THEN SET $var_hull->pageOffset = 0;
     END IF;
	if ($var_hull->pageNumber IS NULL OR $var_hull->pageNumber='') THEN SET $var_hull->pageNumber = 20;
     END IF; 

     SET @SQL = CONCAT('SELECT $fields_list FROM $all_tables $WHERE {$deleting_field_where_string} {$table_relations} ORDER BY ', $var_hull->sortBy, ' ', $var_hull->sortMethod, ' LIMIT ', $var_hull->pageOffset,',', $var_hull->pageNumber);
	
	PREPARE query1 FROM @SQL;
	EXECUTE query1;
	DEALLOCATE PREPARE query1;
	SET $var_hull->numRows = (SELECT COUNT(*) FROM $primary_table_name WHERE $primary_table_name.$var_hull->deletingFieldName = 0); 
END;

EOS_list_paged;
			return $procedure_string;
		}	
		
		/**
		 * Generates stored procedure to insert data
		 *
		 * @param array $data necessary data
		 * @param string &$proc_name here we send name of the procedure
		 * @return string of the SQL-code
		 */
		function GenerateStoredProcedureInsert($data, &$procedure_name)		
		{
			$var_hull = new CodeGeneratorController();
			
			//getting the table_name to sift out fields from other tables
			$primary_table_name = $this->FindTableName($data);
			
			//getting the root of the procedure name from a table name
			$procedure_name = $this->FindProcedureName($var_hull->storedProcedurePrefix, $var_hull->tablePrefix, $primary_table_name, '_insert');		

			//find out if there is field to mark deleted rows
			$has_deleted_field = $this->HasDeletedField($data, $var_hull);	
			
			//creating description of variables, lists of fields, list of variables for insert
			foreach ($data as $value)
			{
				if (($value[table_name] == $primary_table_name) && ($value[column_name] != $var_hull->deletingFieldName) && $value[primary_key] != true)
				{
					$variables_description .= "IN {$value[column_name]} {$value[original_data_type]}";
					if (!empty($value[max_length]) && $value[original_data_type] != 'text') 
						$variables_description .= "({$value[max_length]})";
					$variables_description .= ",\n\t";				
					
					$fields_list .= "{$value[column_name]},\n\t";
					
					$variables_list .= "{$value[column_name]},\n\t";
				}	
			}		
									
			if ($has_deleted_field)
			{
				$fields_list .= ",$var_hull->deletingFieldName";
				$variables_list .= ",0";
			}	
			
			//removing last comma in the lists of variables and fields
			$variables_description =  trim(String::RemoveLastComma($variables_description));			
			$fields_list =  trim(String::RemoveLastComma($fields_list));	
			$variables_list =  trim(String::RemoveLastComma($variables_list));	
			
			//string with info about our generator
			$generated_by = $this->GeneratedBy();

//making the whole string of procedure
$procedure_string =  <<<EOS_insert
-- ================================================
-- $generated_by
-- ================================================
CREATE PROCEDURE `$procedure_name`
(
	$variables_description
)
NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
INSERT INTO $primary_table_name
(
	$fields_list
)
VALUES
(
	$variables_list
);
END;

EOS_insert;
			return $procedure_string;
		}
		
		/**
		 * Generates stored procedure to update data
		 *
		 * @param array $data necessary data
		 * @param string &$proc_name here we send name of the procedure
		 * @return string of the SQL-code
		 */
		function GenerateStoredProcedureUpdate($data, &$procedure_name)		
		{
			$var_hull = new CodeGeneratorController();
			
			//getting the table_name to sift out fields from other tables
			$primary_table_name = $this->FindTableName($data);
			
			//getting the root of the procedure name from a table name
			$procedure_name = $this->FindProcedureName($var_hull->storedProcedurePrefix, $var_hull->tablePrefix, $primary_table_name, '_update');		

			//findung key field
			$key_field = $this->FindKeyField($data);
			
			//creating description of variables
			foreach ($data as $value)
			{
				if (($value[table_name] == $primary_table_name) && ($value[column_name] != $var_hull->deletingFieldName))
				{
					$variables_description .= "IN {$value[column_name]} {$value[original_data_type]}";
					if (!empty($value[max_length]) && $value[original_data_type] != 'text') 
						$variables_description .= "({$value[max_length]})";
					$variables_description .= ",\n\t";				
				}	
			}
			
			//creating lists of fields, list of variables for update
			foreach ($data as $value)
			{
				if (($value[table_name] == $primary_table_name) && ($value[column_name] != $var_hull->deletingFieldName) && $value[primary_key] != true)
				{
					$fields_and_variables_list .= $primary_table_name.".".$value[column_name]." = ".$value[column_name].",\n\t";					
				}	
			}
			
			//removing last comma in the lists of variables and fields
			$variables_description =  trim(String::RemoveLastComma($variables_description));			
			$fields_and_variables_list =  trim(String::RemoveLastComma($fields_and_variables_list));		
			
			//string with info about our generator
			$generated_by = $this->GeneratedBy();

//making the whole string of procedure
$procedure_string =  <<<EOS_update
-- ================================================
-- $generated_by
-- ================================================
CREATE PROCEDURE `$procedure_name`
(
	$variables_description
)
	NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''

BEGIN
UPDATE $primary_table_name
SET
	$fields_and_variables_list
WHERE
	{$this->FindTableName($data)}.$key_field[name] = {$key_field[name]};
END;

EOS_update;
			return $procedure_string;
		}	
				
		/**
		 * Generates stored procedure to delete data
		 *
		 * @param array $data necessary data
		 * @param string &$proc_name here we send name of the procedure
		 * @return string of the SQL-code
		 */
		function GenerateStoredProcedureDelete($data, &$procedure_name)		
		{
			$var_hull = new CodeGeneratorController();
			
			//getting the table_name to sift out fields from other tables
			$primary_table_name = $this->FindTableName($data);
			
			//finding key field
			$key_field = $this->FindKeyField($data);
			
			//getting the root of the procedure name from a table name
			$procedure_name = $this->FindProcedureName($var_hull->storedProcedurePrefix, $var_hull->tablePrefix, $primary_table_name, '_delete');		

			//find out if there is field to mark deleted rows
			$has_deleted_field = $this->HasDeletedField($data, $var_hull);		
			
			//string with info about our generator
			$generated_by = $this->GeneratedBy();	

//making the whole string of procedure depending on existance of deleting field
			if ($has_deleted_field)
			{
							
$procedure_string =  <<<EOS_delete
-- ================================================
-- $generated_by
-- ================================================
CREATE PROCEDURE `$procedure_name`
(
	IN $key_field[name] $key_field[data_type]
)
	NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
UPDATE $primary_table_name
SET
	{$this->FindTableName($data)}.$var_hull->deletingFieldName = 1
WHERE
	{$this->FindTableName($data)}.$key_field[name] = {$key_field[name]};
END;

EOS_delete;
			}
			else 
			{
$procedure_string =  <<<EOS_delete
-- ================================================
-- $generated_by
-- ================================================
CREATE PROCEDURE `$procedure_name`
(
	IN $key_field[name] $key_field[data_type]
)
	NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
DELETE FROM $primary_table_name
WHERE
	{$this->FindTableName($data)}.$key_field[name] = {$key_field[name]};
END;

EOS_delete;
			}
			return $procedure_string;
		}		
		
		/**
		 * Generates stored procedure to get a row of data
		 *
		 * @param array $data necessary data
		 * @param string &$proc_name here we send name of the procedure
		 * @return string of the SQL-code
		 */
		function GenerateStoredProcedureGet($data, &$procedure_name)		
		{
			$var_hull = new CodeGeneratorController();
			
			//getting the table_name to sift out fields from other tables
			$primary_table_name = $this->FindTableName($data);
			
			//finding key field
			$key_field = $this->FindKeyField($data);
			
			//getting the root of the procedure name from a table name
			$procedure_name = $this->FindProcedureName($var_hull->storedProcedurePrefix, $var_hull->tablePrefix, $primary_table_name, '_get');		
			
			//find out if there is field to mark deleted rows
			$has_deleted_field = $this->HasDeletedField($data, $var_hull);	
			
			//find out all tables take part in procedure
			$all_tables = $this->AllTables($data);
			
			//constructing table relations
			$table_relations = $this->TablesRelations($data);
			
			//if we has deleting field, we should add it list of received variabels and WHERE-clause
			$deleting_field_variables_string = '';
			$deleting_field_where_string = '';
			if ($has_deleted_field)
			{
//				$deleting_field_variables_string = $var_hull->deletingFieldName." varchar(10),";
				$deleting_field_where_string = "AND $primary_table_name.$var_hull->deletingFieldName = 0";
			}
			
			//creating description of variables, lists of fields, list of variables for insert
			$fields_list = $this->TablesFields($data);
			
			//string with info about our generator
			$generated_by = $this->GeneratedBy();
			
//making the whole string of procedure
$procedure_string =  <<<EOS_get
-- ================================================
-- $generated_by
-- ================================================
CREATE PROCEDURE `$procedure_name`
(
	IN $key_field[name] $key_field[data_type]
)
	NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
SELECT 
	$fields_list
FROM
	$all_tables
WHERE
	{$this->FindTableName($data)}.$key_field[name] = {$key_field[name]}
	$deleting_field_where_string
	$table_relations;
END;

EOS_get;
			return $procedure_string;
		}	
		
		/**
		 * Generates stored procedure to get a set of data
		 *
		 * @param array $data necessary data
		 * @param string &$proc_name here we send name of the procedure
		 * @return string of the SQL-code
		 */
		function GenerateStoredProcedureList($data, &$procedure_name)		
		{
			$var_hull = new CodeGeneratorController();
			
			//getting the table_name to sift out fields from other tables
			$primary_table_name = $this->FindTableName($data);
			
			//finding key field
			$key_field = $this->FindKeyField($data);
			
			//getting the root of the procedure name from a table name
			$procedure_name = $this->FindProcedureName($var_hull->storedProcedurePrefix, $var_hull->tablePrefix, $primary_table_name, '_list');		
			
			//seeking for sort_by field
			$sort_by_field = $this->FindSortByField($data, $key_field[name], $var_hull);
			
			//find out if there is field to mark deleted rows
			$has_deleted_field = $this->HasDeletedField($data, $var_hull);	
			
			//find out all tables take part in procedure
			$all_tables = $this->AllTables($data);
			
			//constructing table relations
			$table_relations = $this->TablesRelations($data);
			if (!isset($table_relations)) $table_relations = ' ';
			
			//if we has deleting field, we should add it list of received variabels and WHERE-clause
			$deleting_field_variables_string = '';
			$deleting_field_where_string = '';
			$WHERE = '';
			if ($has_deleted_field)
			{
				$deleting_field_variables_string = $var_hull->deletingFieldName." varchar(10),";
				$deleting_field_where_string = "$primary_table_name.$var_hull->deletingFieldName =0', '";
			}
			else
			{
				$deleting_field_variables_string = "";
				$deleting_field_where_string = " 1=1 ', '";
			}
			
			//creating description of variables, lists of fields, list of variables for insert
			$fields_list = $this->TablesFields($data, "", true);
			
			//string with info about our generator
			$generated_by = $this->GeneratedBy();
			
			//check if we should use WHERE-clause
			$WHERE = 'WHERE';
			if (empty($deleting_field_where_string) && empty($table_relations))
				$WHERE = '';
				
			
//making the whole string of procedure
$procedure_string =  <<<EOS_list
-- ================================================
-- $generated_by
-- ================================================
CREATE PROCEDURE `$procedure_name`
(
	IN $var_hull->sortBy varchar(100),
	IN $var_hull->sortMethod varchar(10)
)
	NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

if ($var_hull->sortMethod IS NULL OR $var_hull->sortMethod='') THEN SET $var_hull->sortMethod = 'ASC';
     END IF;
if ($var_hull->sortBy IS NULL OR $var_hull->sortBy='') THEN SET $var_hull->sortBy = '$primary_table_name.$sort_by_field';
     END IF;
  

SET @SQL = CONCAT('SELECT $fields_list FROM $all_tables $WHERE {$deleting_field_where_string} {$table_relations} ORDER BY ', $var_hull->sortBy, ' ', $var_hull->sortMethod);

PREPARE query1 FROM @SQL;
	EXECUTE query1;
	DEALLOCATE PREPARE query1;
END;

EOS_list;
			return $procedure_string;
		}	
		
		
		/**
		 * seeks table_name
		 *
		 * @param array $data necessary data
		 * @return string $table_name
		 */
		function FindTableName($data)
		{
			foreach ($data as $value)
			{
				if ($value[primary_key] == true)	
					$table_name = $value[table_name];
			}	
			return $table_name;
		}
		
		/**
		 * getting the root of the procedure name from a table name
		 *
		 * @param string $stored_procedure_prefix means usually 'sp_', we should add it in the beginning of the proc_name
		 * @param string $table_prefix means usually 't_', we should discard it
		 * @param string $table_name table name
		 * @param string $procedure_action_postfix means predestination of procedure
		 * @return string $procedure_name
		 */
		function FindProcedureName($stored_procedure_prefix, $table_prefix, $table_name, $procedure_action_postfix)
		{
			$procedure_body_name = eregi_replace("^$table_prefix", '', $table_name);
			$procedure_name = $stored_procedure_prefix.$procedure_body_name.$procedure_action_postfix;
			return $procedure_name;
		}
		
			/**
		 * here we find out all fields in the table
		 *
		 * @return string about generating of the code
		 */
		function GeneratedBy()
		{						
			$date = date("d/m/Y H:i");
			$generated = "Generated by SpherusPHPCodegenerator on ".$date;           
			return $generated;
		}	
		
		/**
		 * seeks primary key field in a table fields
		 *
		 * @param array $data necessary data
		 * @return string $key_field
		 */
		function FindKeyField($data)
		{
			foreach ($data as $value)
			{
				if ($value[primary_key] == true)	
				{	
					$key_field[data_type] = $value[original_data_type];
					$key_field[name] = $value[column_name];
				}	
			}	
			return $key_field;
		}
			
		/**
		 * here we check out if the table has field to mark deleted rows
		 *
		 * @param string $data necessary data
		 * @param object $var_hull for getting some string constants
		 * @return boolean if there is deletinf field - return true
		 */
		function HasDeletedField($data, $var_hull)
		{			
			foreach ($data as $value)
			{
				if ($value[column_name] == $var_hull->deletingFieldName)
					return true;
			}	
			return false;
		}		
		
		/**
		 * here we find out all tables, which is mentioned in $data
		 *
		 * @param string $data necessary data
		 * @return string all tables in the string with commas
		 */
		function AllTables($data)
		{			
			foreach ($data as $value)
			{
				$columns[] = $value[table_name];
			}	
			$columns = array_unique($columns);
			$columns = implode(', ', $columns);
			return $columns;
		}	
		
		/**
		 * here we find out all tables relations
		 *
		 * @param string $data necessary data
		 * @return string all tables are now intertwined
		 */
		function TablesRelations($data)
		{						
			foreach ($data as $value)
			{
				if (!empty($value[foreign_table]))
				{
					$relationships_string .= "AND $value[table_name].$value[column_name] = $value[foreign_table].$value[column_name] ', '";		
				}
            }
            return $relationships_string;
		}	
		
		/**
		 * here we find out all fields in the table
		 *
		 * @param string $data necessary data
		 * @param string $indent for goodlooking coding
		 * @param boolean $for_concat if we should place it in one row
		 * @return string all fields with table_names separated by commas
		 */
		function TablesFields($data, $indent = '', $for_concat = false)
		{			
			$new_row = "\n\t";				
			if ($for_concat)
			{
				$new_row = "";
			}
            foreach ($data as $value)
			{
				$fields_list .= "$value[table_name].$value[column_name],{$new_row}{$indent}";
			}		
				
			$fields_list =  trim(String::RemoveLastComma($fields_list));
			return $fields_list;
		}	
		
				/**
		 * getting the 'sort by' field: seeking for a field which ends with '_name' (by default) and root is as in key field
		 * if search is failed, we send key field name
		 *
		 * @param string $data necessary data
		 * @param string $key_field_name means key field name
		 * @param object $var_hull for getting some string constants
		 * @return string $value[column_name] | $key_field_name
		 */
		function FindSortByField($data, $key_field_name, $var_hull)
		{
			$root_field_name = trim(String::RemoveLastSubstring($key_field_name, $var_hull->keyFieldPostfix));	
			
			foreach ($data as $value)
			{
				if ($value[column_name] == ($root_field_name.$var_hull->nameFieldPostfix))
					return $value[column_name];
			}	
			return $key_field_name;
		}
		
		function CreateStoredProcedures(array $stored_procedures)
		{
			global $current_controller;
			foreach ($stored_procedures as $key=>$value)
			{
				
				$dropSQL = "DROP PROCEDURE IF EXISTS $key";
				
				//set ANSI_NULLS ON (because parameters for filtering often receive NULL)
//				$current_controller->controller_model->Execute('', '', 'set ANSI_NULLS ON set QUOTED_IDENTIFIER ON');
				
				//erase previous procedures
				$current_controller->controller_model->Execute('', '', $dropSQL);

				//create new procedures
				$current_controller->controller_model->Execute('', '', $value);
			}
		}		
	}	
?>